package Final;

import java.rmi.StubNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
class STUDENT {
	int id;
	String stuno ;
	String name;
	int age;
	String birthday;

	public STUDENT(int id, String stuno,String name, int age, String birthday) {
		super();
		this.id = id;
		this.stuno = stuno;
		this.name = name;
		this.age = age;
		this.birthday = birthday;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getStuno() {
		return stuno;
	}

	public void setStuno(String stuno) {
		this.stuno = stuno;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}
}

public class Pre201521123082 {
	static Connection connection = null;
	static PreparedStatement pStatement = null;
	static ResultSet resultSet = null;
	ArrayList<STUDENT> list =new ArrayList<>();
	public static STUDENT getStudentByName(String name) throws SQLException {
		String sql = "select * from student where name = ?";
		pStatement = connection.prepareStatement(sql);
		pStatement.setString(1, name);
		resultSet = pStatement.executeQuery();
		STUDENT student = new STUDENT(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3),
				resultSet.getInt(4), resultSet.getDate(5).toString());
		return student;
	}
	public static List<STUDENT> getStudentsByAgeBelow(int age) throws SQLException {
		ArrayList<STUDENT> list =new ArrayList<>();
		String sql = "select * from student where age < ?";
		pStatement = connection.prepareStatement(sql);
		pStatement.setInt(1, age);
		resultSet = pStatement.executeQuery();
		while(resultSet.next()){
			STUDENT stu = new STUDENT(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3),
					resultSet.getInt(4), resultSet.getDate(5).toString());
			list.add(stu);
		}
		return list;
	}

	public static void displayStudentBetween(String begin, String end) throws SQLException {
		String sql = "select * from student where birthday between ? and ?;";
		pStatement = connection.prepareStatement(sql);
		pStatement.setString(1, begin);
		pStatement.setString(2, end);
		resultSet = pStatement.executeQuery();
		System.out.println("id\tstuno\t\tname\t\tage");
		while (resultSet.next()) {
			System.out.print(resultSet.getInt("id")+"\t");
			System.out.print(resultSet.getString("stuno")+"\t\t");
			System.out.print(resultSet.getString("name")+"\t\t");
			System.out.println(resultSet.getInt("age"));
		}
	}

	public static double getAvgAbove(int age) throws SQLException {
		String sql="select avg(age) avgAge from student where age>? ";
		pStatement = connection.prepareStatement(sql);
		pStatement.setInt(1, age);
		resultSet = pStatement.executeQuery();
		return resultSet.getDouble("avgAge");
	}

	public static void main(String[] args) throws SQLException {

		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
		String userName = "root";
		String password = "19960908";
		connection = DriverManager.getConnection(url, userName, password);
		String strSql = "insert into student(id,stuno,name,age,birthday) values(?,?,?,?,?)";
		pStatement = connection.prepareStatement(strSql);
		
	}

}
